package com.sixj.keycloak;

import org.jboss.logging.Logger;
import org.keycloak.component.ComponentModel;
import org.keycloak.models.KeycloakSession;
import org.keycloak.models.RealmModel;
import org.keycloak.models.UserModel;

import java.sql.*;
import java.util.*;

public class EmployeeUserStorageDao {
    private static final Logger logger = Logger.getLogger(EmployeeUserStorageDao.class);

    private ComponentModel model;
    private String jdbcUrl;
    private String jdbcUser;
    private String jdbcPassword;

    public EmployeeUserStorageDao(ComponentModel model) {
        this.model = model;
        // 从ComponentModel参数获取JDBC连接配置
        this.jdbcUrl = model.getConfig().getFirst("jdbcUrl");
        this.jdbcUser = model.getConfig().getFirst("jdbcUser");
        this.jdbcPassword = model.getConfig().getFirst("jdbcPassword");
    }

    /** 按工号查询用户 */
    public Employee getByEmpNoOrEmail(String key) {
        String sql = "SELECT * FROM employee WHERE emp_no=? LIMIT 1";
        logger.infof("getByEmpNoOrEmail: %s , key: %s", sql, key);
        Employee employee = queryOne(sql, key);
        logger.infof("getByEmpNoOrEmail: %s", employee);
        return employee;
    }

    /** 按ID查询用户 */
    public Employee getById(String id) {
        String sql = "SELECT * FROM employee WHERE id=? LIMIT 1";
        logger.infof("getById: %s , id: %s", sql, id);
        Employee employee = queryOne(sql, id);
        logger.infof("getById: %s", employee);
        return employee;
    }

    /** 查询全部用户 */
    public List<Employee> getUserList() {
        String sql = "SELECT * FROM employee WHERE deleted=0";
        logger.infof("getUserList: %s", sql);
        List<Employee> employees = queryList(sql);
        logger.infof("getUserList: %s", employees);
        return employees;
    }

    /** 分页查询 */
    public List<Employee> getUserList(int start, int offset) {
        String sql = "SELECT * FROM employee WHERE deleted=0 LIMIT ?, ?";
        logger.infof("getUserList: %s , start: %s, offset: %s", sql, start, offset);
        List<Employee> employees = queryList(sql, start, offset);
        logger.infof("getUserList: %s", employees);
        return employees;
    }


    /** 按工号或邮箱模糊查询 */
    public List<Employee> getByKeyword(String keyword) {
        String sql = "SELECT * FROM employee WHERE (emp_no LIKE ? OR emp_name LIKE ?) AND deleted=0 ";
        String pattern = "%" + keyword + "%";
        logger.infof("getByKeyword: %s , keyword: %s", sql, pattern);
        List<Employee> employees = queryList(sql, pattern, pattern);
        logger.infof("getByKeyword: %s", employees);
        return employees;
    }

    /** 按工号或邮箱模糊分页 */
    public List<Employee> getByKeyword(String keyword, int start, int offset) {
        String sql = "SELECT * FROM employee WHERE (emp_no LIKE ? OR emp_name LIKE ?) AND deleted=0 LIMIT ?, ?";
        String pattern = "%" + keyword + "%";
        logger.infof("getByKeyword: %s , keyword: %s, start: %s, offset: %s", sql, pattern, start, offset);
        List<Employee> employees = queryList(sql, pattern, pattern, start, offset);
        logger.infof("getByKeyword: %s", employees);
        return employees;
    }

    /** 用户总数 */
    public int count() {
        String sql = "SELECT COUNT(1) FROM employee WHERE deleted=0 ";
        logger.infof("count: %s", sql);
        try (Connection c = getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ResultSet rs = ps.executeQuery();
            if (rs.next()){
                int anInt = rs.getInt(1);
                logger.infof("count: %s", anInt);
                return anInt;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    // =============== JDBC核心方法 ===============

    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPassword);
    }

    /** 查询单个用户 */
    private Employee queryOne(String sql, Object... params) {
        try (Connection c = getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            setParams(ps, params);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) return toEmployee(rs);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /** 查询列表 */
    private List<Employee> queryList(String sql, Object... params) {
        List<Employee> list = new ArrayList<>();
        try (Connection c = getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            setParams(ps, params);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                list.add(toEmployee(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    private void setParams(PreparedStatement ps, Object... params) throws SQLException {
        if (params != null) {
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }
        }
    }

    private Employee toEmployee(ResultSet rs) throws SQLException {
        Employee emp = new Employee();
        emp.setId(rs.getString("id"));
        emp.setEmpNo(rs.getString("emp_no"));
        emp.setEmpName(rs.getString("emp_name"));
        emp.setEmail(rs.getString("email"));
        emp.setMobile(rs.getString("mobile"));
        emp.setPassword(rs.getString("password"));
        // 可补充更多字段映射
        return emp;
    }

    public static List<UserModel> toUserModels(List<Employee> employees, KeycloakSession session, RealmModel realm, ComponentModel model) {
        if (employees == null || employees.isEmpty()) return Collections.emptyList();
        List<UserModel> allUsers = new ArrayList<>();
        for (Employee emp : employees) {
            allUsers.add(new EmployeeUserAdapter(session, realm, model, emp));
        }
        return allUsers;
    }

}
